package com.bci.pwtz.common.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.io.FilenameUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

// 读取Excel文件
public class ExcelUtil
{

    public static List<String> readExcelByFirstRow(String url) throws FileNotFoundException, IOException
    {
        File file = new File(url);
        List<String> result = new ArrayList<String>();
        if (file.getPath().endsWith(".xls"))
        {
            result = read2003ExcelByFirstRow(file);
        }
        else if (file.getPath().endsWith(".xlsx"))
        {
            result = read2007ExcelByFirstRow(file);
        }
        return result;
    }

    public static List<String[]> readExcel(File file) throws FileNotFoundException, IOException
    {
        List<String[]> result = new ArrayList<String[]>();
        if (file.getPath().endsWith(".xls"))
        {
            result = read2003Excel(file);
        }
        else if (file.getPath().endsWith(".xlsx"))
        {
            result = read2007Excel(file);
        }
        return result;
    }

    public static List<String> read2007ExcelByFirstRow(File file) throws FileNotFoundException, IOException
    {
        List<String> result = new ArrayList<String>();
        XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
        XSSFSheet sheet = xwb.getSheetAt(0);
        for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++)
        {
            XSSFRow row = sheet.getRow(rowIndex);
            if (row == null)
            {
                continue;
            }
            // String[] rowArray = new String[sheet.getRow(0).getLastCellNum()];
            // for(short
            // columnIndex=0;columnIndex<row.getLastCellNum();columnIndex++){
            String value = "";
            XSSFCell cell = row.getCell(0);// 得到的每行的每个具体的值
            if (cell != null)
            {
                switch (cell.getCellType())
                {
                    case XSSFCell.CELL_TYPE_STRING:
                        value = cell.getStringCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_NUMERIC:
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");// 格式化日期字符串
                        if ("General".equals(cell.getCellStyle().getDataFormatString()))
                        {
                            value = new DecimalFormat("0").format(cell.getNumericCellValue());
                        }
                        else
                        {
                            value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                        }
                        break;
                    case XSSFCell.CELL_TYPE_FORMULA:
                        // 导入时如果为公式生成的数据则无值
                        if (!cell.getStringCellValue().equals(""))
                        {
                            value = cell.getStringCellValue();
                        }
                        else
                        {
                            value = cell.getNumericCellValue() + "";
                        }
                        break;
                    case XSSFCell.CELL_TYPE_BLANK:
                        break;
                    case XSSFCell.CELL_TYPE_ERROR:
                        value = "";
                        break;
                    case XSSFCell.CELL_TYPE_BOOLEAN:
                        value = (cell.getBooleanCellValue() == true ? "Y" : "N");
                        break;
                    default:
                        value = "";
                        break;
                }
                result.add(value);
            }
        }
        return result;
    }

    public static List<String[]> read2007Excel(File file) throws FileNotFoundException, IOException
    {
        List<String[]> result = new ArrayList<String[]>();
        XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
        XSSFSheet sheet = xwb.getSheetAt(0);
        for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++)
        {
            XSSFRow row = sheet.getRow(rowIndex);
            if (row == null)
            {
                continue;
            }
            String[] rowArray = new String[sheet.getRow(0).getLastCellNum()];
            for (short columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++)
            {
                String value = "";
                XSSFCell cell = row.getCell(columnIndex);// 得到的每行的每个具体的值
                if (cell != null)
                {
                    switch (cell.getCellType())
                    {
                        case XSSFCell.CELL_TYPE_STRING:
                            value = cell.getStringCellValue();
                            break;
                        case XSSFCell.CELL_TYPE_NUMERIC:
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");// 格式化日期字符串
                            if ("General".equals(cell.getCellStyle().getDataFormatString()))
                            {
                                value = new DecimalFormat("0").format(cell.getNumericCellValue());
                            }
                            else
                            {
                                value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                            }
                            break;
                        case XSSFCell.CELL_TYPE_FORMULA:
                            // 导入时如果为公式生成的数据则无值
                            if (!cell.getStringCellValue().equals(""))
                            {
                                value = cell.getStringCellValue();
                            }
                            else
                            {
                                value = cell.getNumericCellValue() + "";
                            }
                            break;
                        case XSSFCell.CELL_TYPE_BLANK:
                            break;
                        case XSSFCell.CELL_TYPE_ERROR:
                            value = "";
                            break;
                        case XSSFCell.CELL_TYPE_BOOLEAN:
                            value = (cell.getBooleanCellValue() == true ? "Y" : "N");
                            break;
                        default:
                            value = "";
                            break;
                    }
                }
                rowArray[columnIndex] = value;
            }
            result.add(rowArray);
        }
        return result;
    }

    public static List<String> read2003ExcelByFirstRow(File file) throws FileNotFoundException, IOException
    {
        List<String> result = new ArrayList<String>();
        // 创建新的 excel工作簿
        HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
        HSSFSheet sheet = hwb.getSheetAt(0);
        // 循环每行
        for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++)
        {
            HSSFRow row = sheet.getRow(rowIndex);// 得到每一行的数据
            if (row == null)
            {
                continue;
            }
            // 循环每列
            // for (short columnIndex = 0; columnIndex <row.getLastCellNum();
            // columnIndex++) {
            String value = "";
            HSSFCell cell = row.getCell(0);
            if (cell != null)
            {
                switch (cell.getCellType())
                {
                    case XSSFCell.CELL_TYPE_STRING:
                        value = cell.getStringCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_NUMERIC:
                        if (HSSFDateUtil.isCellDateFormatted(cell))
                        {
                            Date date = cell.getDateCellValue();
                            if (date != null)
                            {
                                value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                            }
                            else
                            {
                                value = "";
                            }
                        }
                        else
                        {
                            value = new DecimalFormat("0").format(cell.getNumericCellValue());
                        }
                        break;
                    case XSSFCell.CELL_TYPE_FORMULA:
                        // 导入时如果为公式生成的数据则无值
                        if (!cell.getStringCellValue().equals(""))
                        {
                            value = cell.getStringCellValue();
                        }
                        else
                        {
                            value = cell.getNumericCellValue() + "";
                        }
                        break;
                    case XSSFCell.CELL_TYPE_BOOLEAN:
                        value = (cell.getBooleanCellValue() == true ? "Y" : "N");
                        break;
                    case XSSFCell.CELL_TYPE_BLANK:
                        value = "";
                        break;
                    case XSSFCell.CELL_TYPE_ERROR:
                        value = "";
                        break;
                    default:
                        value = cell.toString();
                }
                // }
                result.add(value);
            }

        }
        return result;
    }

    public static List<String[]> read2003Excel(File file) throws FileNotFoundException, IOException
    {
        List<String[]> result = new ArrayList<String[]>();
        // 创建新的 excel工作簿
        HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
        HSSFSheet sheet = hwb.getSheetAt(0);
        // 循环每行
        for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++)
        {
            HSSFRow row = sheet.getRow(rowIndex);// 得到每一行的数据
            if (row == null)
            {
                continue;
            }
            String[] rowArray = new String[sheet.getRow(0).getLastCellNum()];
            // 循环每列
            for (short columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++)
            {
                String value = "";
                HSSFCell cell = row.getCell(columnIndex);
                if (cell != null)
                {
                    switch (cell.getCellType())
                    {
                        case XSSFCell.CELL_TYPE_STRING:
                            value = cell.getStringCellValue();
                            break;
                        case XSSFCell.CELL_TYPE_NUMERIC:
                            if (HSSFDateUtil.isCellDateFormatted(cell))
                            {
                                Date date = cell.getDateCellValue();
                                if (date != null)
                                {
                                    value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                                }
                                else
                                {
                                    value = "";
                                }
                            }
                            else
                            {
                                value = new DecimalFormat("0").format(cell.getNumericCellValue());
                            }
                            break;
                        case XSSFCell.CELL_TYPE_FORMULA:
                            // 导入时如果为公式生成的数据则无值
                            if (!cell.getStringCellValue().equals(""))
                            {
                                value = cell.getStringCellValue();
                            }
                            else
                            {
                                value = cell.getNumericCellValue() + "";
                            }
                            break;
                        case XSSFCell.CELL_TYPE_BOOLEAN:
                            value = (cell.getBooleanCellValue() == true ? "Y" : "N");
                            break;
                        case XSSFCell.CELL_TYPE_BLANK:
                            value = "";
                            break;
                        case XSSFCell.CELL_TYPE_ERROR:
                            value = "";
                            break;
                        default:
                            value = cell.toString();
                    }
                }
                rowArray[columnIndex] = value;
            }
            result.add(rowArray);
        }
        return result;
    }

    /**
     * 删除临时文件 <br/>
     * <p>
     * <br/>
     * <p>
     * Author: arming<br/>
     * <p>
     * Date: 2013年12月28日-上午10:36:35<br/>
     * <p>
     * 
     * @param dir
     * 
     */
    public static void cleanTempFile(String dir)
    {
        if (StringUtils.isBlank(dir))
            return;
        File fd = new File(dir);
        if (fd == null)
            return;
        File[] files = fd.listFiles();
        if (files == null)
            return;
        for (File f : files)
        {
            if (f.isFile() && (System.currentTimeMillis() - f.lastModified() > 600000))
            {
                try
                {
                    if (FilenameUtils.getExtension(f.getName()).toLowerCase().equals("xls"))
                        f.delete();
                }
                catch (Exception e)
                {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void main(String[] args) throws IOException
    {
//        List<Excelable> list = new ArrayList<Excelable>();
//        ToMerchantDaliyEarningsDetail o = new ToMerchantDaliyEarningsDetail();
//        o.setDaliyEarningsDate(new java.util.Date());
//        o.setMerchantCode("100000-201");
//        o.setMerchantName("测试商家111");
//        o.setSettlementTotalAmount(10000.12f);
//        list.add(o);
//
//        System.out.println(ExcelUtil.write(list, "E:/", "yyyy-MM-dd"));
    }
}
